﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using System.Text;
using BTAlertWinService.Models;
using BTAlertWinService.Logging;
namespace BTAlertWinService.DBOps
{
    public class BTOPerations
    {
        public static IList<ReceiveLocations> fetchReceiveLocations()
        {
            
            FileLogger.Log("fetchReceiveLocations has started.");

            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<ReceiveLocations> locations = null;
            query.Append("SELECT RL.Description, APP.nvcName as ApplicationName, RL.Name AS LocationName, RL.InboundTransportURL AS InboundTransportURL, RP.nvcName AS PortName, RL.Disabled AS PortDisabled FROM BizTalkMgmtDb.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK) INNER JOIN BizTalkMgmtDb.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK) ON RL.ReceivePortId = RP.nID INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK) ON RP.nApplicationID = APP.nID AND (RL.Description not like '%[DISABLED]%' or RL.Description is null)");

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                locations = new List<ReceiveLocations>();

                while (reader.Read())
                {
                    locations.Add(new ReceiveLocations()
                    {
                        InboundTransportURL = Convert.ToString(reader["InboundTransportURL"]),
                        ApplicationName = Convert.ToString(reader["ApplicationName"]),
                        LocationName = Convert.ToString(reader["LocationName"]),
                        PortName = Convert.ToString(reader["PortName"]),
                        PortDisabled = Convert.ToBoolean(reader["PortDisabled"]),
                        Description = Convert.ToString(reader["Description"])
                    });
                }
                conn.Close();
            }
            FileLogger.Log("fetchReceiveLocations has Successful.");

            return locations;
        }


        public static IList<SendLoctions> fetchSendLocations()
        {
            List<SendLoctions> locations = null;

            StringBuilder query = new StringBuilder();
            query.Append("SELECT APP.nvcName AS AppName, SP.nvcName AS LocationName, case SP.nPortStatus WHEN 3 THEN 'Started' WHEN 1 THEN 'Unenlisted' WHEN 2 THEN 'Stopped' WHEN 4 THEN 'Enabled' WHEN 5 THEN 'Disabled' END AS PortStatus, SPTP.nvcAddress AS OutboundTransportURL FROM BizTalkMgmtDb.dbo.bts_sendport AS SP INNER JOIN  BizTalkMgmtDb.dbo.bts_sendport_transport AS SPTP WITH(READPAST, ROWLOCK) ON SPTP.nSendPortID = SP.nID AND SPTP.nSendHandlerID IS NOT NULL INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK) ON SP.nApplicationID = APP.nID AND (SP.nvcDescription not like '%[DISABLED]%' or SP.nvcDescription is null) and (SP.nPortStatus = 2 or SP.nPortStatus = 1)");

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                locations = new List<SendLoctions>();

                while (reader.Read())
                {
                    locations.Add(new SendLoctions()
                    {
                        LocationName = Convert.ToString(reader["LocationName"]),
                        OutboundTransportURL = Convert.ToString(reader["OutboundTransportURL"]),
                        PortStatus = Convert.ToString(reader["PortStatus"]),
                        ApplicationName = Convert.ToString(reader["AppName"])
                    });
                }
                conn.Close();
            }

            return locations;
        }


        public static string connStrings = null;
        static BTOPerations()
        {

            connStrings = System.Configuration.ConfigurationManager.ConnectionStrings["BiztalkConnection"].ConnectionString;
        }

    }

}
